/*
 * Copyright 2004-2011 Paolo Conte
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.jellylab.ojoverse;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Date;
import java.sql.ParameterMetaData;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Model {

    private transient Connection cnn;
    public static final int OPERATOR_UNDEFINED = 0;
    public static final int OPERATOR_EQUAL = 1;
    public static final int OPERATOR_GREATER = 2;
    public static final int OPERATOR_GREATEREQUAL = 3;
    public static final int OPERATOR_LESSER = 4;
    public static final int OPERATOR_LESSEREQUAL = 5;
    public boolean debug = false;

    public Model(Connection cnn) {
        this.cnn = cnn;
    }

    /**
     * @return Statement
     */
    protected Statement getStatement() throws SQLException {
        Statement stmt = cnn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        return stmt;
    }

    /**
     * @param SQL query
     * @return PreparedStatement
     */
    protected PreparedStatement getPreparedStatement(String query) throws SQLException {
        PreparedStatement prstm = null;
        prstm = cnn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        // SQLite works only with TYPE_FORWARD_ONLY:
        // prstm = cnn.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY);

        return prstm;
    }

    public boolean beginTransaction() {
        boolean beginOK = true;
        try {
            cnn.setAutoCommit(false);
            cnn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        } catch (SQLException sqlex) {
            beginOK = false;
            log(sqlex.getMessage());
        }
        return beginOK;
    }

    public boolean commitTransaction() {
        boolean commitOK = true;
        try {
            cnn.commit();
        } catch (SQLException sqlex) {
            commitOK = false;
            log(sqlex.getMessage());
        }
        return commitOK;
    }

    public boolean rollbackTransaction() {
        boolean rollbackOK = true;
        try {
            cnn.rollback();
        } catch (SQLException sqlex) {
            rollbackOK = false;
            log(sqlex.getMessage());
        }
        return rollbackOK;
    }

    private String getOperator(int operatorType) {
        String operator = null;
        switch (operatorType) {
            case OPERATOR_EQUAL:
                operator = "=";
                break;
            case OPERATOR_GREATER:
                operator = ">";
                break;
            case OPERATOR_GREATEREQUAL:
                operator = ">=";
                break;
            case OPERATOR_LESSER:
                operator = "<";
                break;
            case OPERATOR_LESSEREQUAL:
                operator = "<=";
                break;
        }
        return operator;
    }

    public Message select(DataWrapper wrapper) {
        return select(wrapper, null);
    }

    public Message select(DataWrapper wrapper, DataObject data) {
        return select(wrapper, data, OPERATOR_EQUAL);
    }

    public Message select(DataWrapper wrapper, DataObject data, int operatorType) {
        return select(wrapper, data, null, operatorType, OPERATOR_UNDEFINED);
    }

    public Message select(DataWrapper wrapper, DataObject dataFrom, DataObject dataTo) {
        return select(wrapper, dataFrom, dataTo, OPERATOR_UNDEFINED, OPERATOR_UNDEFINED);
    }

    public Message select(DataWrapper wrapper, DataObject dataFrom, DataObject dataTo, int operatorTypeFrom, int operatorTypeTo) {
        String query = wrapper.getSelectSQL();
        String where = "";
        Message message = null;

        try {
            where = createWhere(wrapper, dataFrom, dataTo, operatorTypeFrom, operatorTypeTo);
            log("where: " + where);
        } catch (IllegalArgumentException iarexc) {
            message = new Message("createWhere IllegalArgumentException", Message.ERROR, iarexc);
            log("select IllegalArgumentException: " + iarexc.toString());
        } catch (IllegalAccessException iacexc) {
            message = new Message("createWhere IllegalAccessException", Message.ERROR, iacexc);
            log("select IllegalAccessException: " + iacexc.toString());
        }

        if (message == null) {
            DataObject[] dati = null;

            PreparedStatement prstm = null;
            try {
                prstm = getPreparedStatement(query + where);
                if (where.length() > 0) {
                    prstm = prepareSelectStatement(prstm, wrapper, dataFrom, dataTo);
                }
            } catch (SQLException sqlexc) {
                log("select where SQLException: " + sqlexc.toString());
            } catch (IllegalAccessException iacexc) {
                log("select where IllegalAccessException: " + iacexc.toString());
            }

            ResultSet rs = null;
            try {
                rs = prstm.executeQuery();
            } catch (SQLException sqlexc) {
                message = new Message("executeQuery SQLException", Message.ERROR, sqlexc);
                log("select execute: " + sqlexc.toString());
                log(prstm.toString());
            }

            if (rs != null) {
                try {
                    dati = loadData(wrapper, rs);
                } catch (SQLException sqlexc) {
                    message = new Message("loadData SQLException", Message.ERROR, sqlexc);
                    log("select loadData: " + sqlexc.toString());
                } catch (IllegalAccessException iacexc) {
                    message = new Message("loadData IllegalAccessException", Message.ERROR, iacexc);
                    log("select loadData: " + iacexc.toString());
                } finally {
                    try {
                        rs.close();
                        prstm.close();
                    } catch (SQLException sqlexc) {
                        message = new Message("loadData IllegalAccessException", Message.WARNING, sqlexc);
                        log("select close: " + sqlexc.toString());
                    }
                }
            }
            message = new Message("select", Message.OK, dati);
        }

        return message;
    }

    public Message insert(DataWrapper wrapper, DataObject data) {

        List groups = wrapper.getGroups();
        Message message = new Message("insert", Message.WARNING, "No groups were found.");

        Iterator iterGroups = groups.iterator();
        while (iterGroups.hasNext()) {
            String group = (String) iterGroups.next();
            message = insert(wrapper, data, group);
            log(message.getName() + "=" + message.getMessage() + " rows");
            if (message.isKO()) {
                break;
            }
        }
        return message;
    }

    public Message insert(DataWrapper wrapper, DataObject data, String group) {
        StringBuffer query = new StringBuffer(300);
        Message message = new Message();

        query.append("INSERT INTO ").append(group).append(" (");
        //.append(getFieldList(dati)).append(") VALUES (");

        Field[] fields = data.getDataFields();

        if (fields == null) {
            return new Message("insert", Message.ERROR, "Fields array cannot be null.");
        }

        int fieldLen = fields.length;
        try {
            StringBuffer fieldNames = new StringBuffer(300);
            StringBuffer values = new StringBuffer(300);
            for (int idf = 0; idf < fieldLen; idf++) {
                Field field = fields[idf];
                String fieldName = field.getName();
                Object fieldValue = field.get(data);
                Column column = getColumn(wrapper, fieldName);
                String columnsName = column.getDBName();
                String columnGroup = wrapper.getGroup(column.getGroup());
                if (columnGroup.equals(group)) {
                    // TODO: verificare se, e come, gestire i valori null (per settare a null un campo del DB)
                    if (fieldValue != null) {
                        if (fieldNames.length() > 0) {
                            fieldNames.append(", ");
                        }
                        fieldNames.append(columnsName);
                        if (values.length() > 0) {
                            values.append(", ");
                        }
                        values.append("?");
                    }
                }
            }
            query.append(fieldNames);
            query.append(") VALUES (");
            query.append(values);
            query.append(")");
        } catch (IllegalArgumentException iaexc) {
            message = new Message("insert IllegalArgumentException", Message.ERROR, iaexc);
        } catch (IllegalAccessException iaexc) {
            message = new Message("insert IllegalAccessException", Message.ERROR, iaexc);
        }

        PreparedStatement prstm = null;
        if (message.isOK()) {
            try {
                prstm = getPreparedStatement(query.toString());
            } catch (SQLException sqlexc) {
                message = new Message("insert getPreparedStatement SQLException", Message.ERROR, sqlexc);
            }
            if (message.isOK()) {
                log(query);
                try {
                    prstm = prepareExecuteStatement(prstm, wrapper, data, group);
                } catch (SQLException sqlexc) {
                    message = new Message("insert prepareExecuteStatement SQLException", Message.ERROR, sqlexc);
                } catch (IllegalAccessException iacexc) {
                    message = new Message("insert IllegalAccessException", Message.ERROR, iacexc);
                }
            }
        }

        if (message.isOK()) {
            try {
                int rows = prstm.executeUpdate();
                message = new Message("insert", Message.OK, new Integer(rows));
            } catch (SQLException sqlexc) {
                message = new Message("insert executeUpdate SQLException", Message.ERROR, sqlexc);
            }
        }

        return message;
    }

    private String createWhere(DataWrapper wrapper, DataObject dataFrom, DataObject dataTo, int operatorTypeFrom, int operatorTypeTo) throws IllegalArgumentException, IllegalAccessException {

        if (dataFrom != null && dataTo != null) {
            if (!dataFrom.getClass().getName().equals(dataTo.getClass().getName())) {
                throw new IllegalArgumentException("Trying to compare different DataObjects.");
            }
            if (dataFrom.getDataFields() == null || dataTo.getDataFields() == null) {
                throw new IllegalArgumentException("Fields array " + ((dataFrom.getDataFields() == null) ? "FROM" : "TO") + " cannot be null.");
            }
            if (dataFrom.getDataFields().length != dataTo.getDataFields().length) {
                throw new IllegalArgumentException("Fields number in WHERE DataObjects not equal.");
            }
        }

        if (dataFrom == null && dataTo == null) {
            return "";
        }

        StringBuffer where = new StringBuffer(1000);
        where.append(" WHERE ");

        if (dataFrom != null) {
            int operatorType = operatorTypeFrom;
            if (operatorType == OPERATOR_UNDEFINED) {
                if (dataTo != null) {
                    if (operatorTypeTo == OPERATOR_UNDEFINED || operatorTypeTo == OPERATOR_LESSEREQUAL) {
                        operatorType = OPERATOR_GREATER;
                    } else {
                        operatorType = OPERATOR_GREATEREQUAL;
                    }
                }
            }
            where.append(createWhere(wrapper, dataFrom, operatorType));
        }
        if (dataTo != null) {
            if (where.length() > 7) {
                where.append(" AND ");
            }
            int operatorType = operatorTypeTo;
            if (operatorType == OPERATOR_UNDEFINED) {
                if (dataFrom != null) {
                    if (operatorTypeFrom == OPERATOR_UNDEFINED || operatorTypeFrom == OPERATOR_GREATEREQUAL) {
                        operatorType = OPERATOR_LESSER;
                    } else {
                        operatorType = OPERATOR_LESSEREQUAL;
                    }
                }
            }
            where.append(createWhere(wrapper, dataTo, operatorType));
        }

        return where.toString();
    }

    private String createWhere(DataWrapper wrapper, DataObject data, int operatorType) throws IllegalArgumentException, IllegalAccessException {
        Field[] fields = data.getDataFields();

        if (fields == null) {
            throw new IllegalArgumentException("Fields array cannot be null.");
        }

        int fieldLen = fields.length;

        String operator = getOperator(operatorType);

        StringBuffer where = new StringBuffer(1000);
        for (int idf = 0; idf < fieldLen; idf++) {
            Field field = fields[idf];
            String fieldName = field.getName();
            Column column = getColumn(wrapper, fieldName);
            int fieldType = column.datatype.getType();	//fieldFrom.getType().getName();

            Object fieldValue = field.get(data);

            if (DataWrapper.isType(fieldType, Types.VARCHAR)) {		//fieldType.equals("java.lang.String")
                if (fieldValue != null && ((String) fieldValue).indexOf('%') >= 0) {
                    operator = " LIKE ";
                }
            }

            // consider only fields not null
            if (fieldValue != null) {
                if (where.length() > 0) {
                    where.append(" AND ");
                }
                where.append(column.getDBName());
                where.append(operator);
                where.append('?');
            }
        }

        return where.toString();
    }

    private PreparedStatement prepareSelectStatement(PreparedStatement prstm, DataWrapper wrapper, DataObject dataFrom, DataObject dataTo) throws SQLException, IllegalAccessException {

        if (dataFrom == null && dataTo == null) {
            return prstm;
        }

        int prepIdx = 1;
        if (dataFrom != null) {
            prstm = prepareSelectStatement(prstm, prepIdx, wrapper, dataFrom);
        }
        ParameterMetaData params = prstm.getParameterMetaData();
        prepIdx = params.getParameterCount();
        if (dataTo != null) {
            prstm = prepareSelectStatement(prstm, prepIdx, wrapper, dataTo);
        }

        return prstm;
    }

    private PreparedStatement prepareExecuteStatement(PreparedStatement prstm, DataWrapper wrapper, DataObject data, String group) throws SQLException, IllegalAccessException {
        return prepareStatement(prstm, 1, wrapper, data, group);
    }

    private PreparedStatement prepareSelectStatement(PreparedStatement prstm, int prepIdx, DataWrapper wrapper, DataObject data) throws SQLException, IllegalAccessException {
        return prepareStatement(prstm, prepIdx, wrapper, data, null);
    }

    private PreparedStatement prepareStatement(PreparedStatement prstm, int prepIdx, DataWrapper wrapper, DataObject data, String group) throws SQLException, IllegalAccessException {
        Field[] fields = data.getDataFields();

        int fieldLen = fields.length;

        for (int idf = 0; idf < fieldLen; idf++) {
            Field field = fields[idf];
            String fieldName = field.getName();
            Column column = getColumn(wrapper, fieldName);
            int fieldType = column.datatype.getType();
            String columnGroup = wrapper.getGroup(column.getGroup());
            if (group == null || columnGroup.equals(group)) {
                Object fieldValue = field.get(data);

                // consider only not null fields
                if (fieldValue == null) {
                    continue;
                }

                switch (fieldType) {
                    case Types.CHAR:
                    case Types.VARCHAR:
                        prstm.setString(prepIdx++, (String) fieldValue);
                        break;
                    case Types.DATE:
                    case Types.TIME:
                        prstm.setDate(prepIdx++, (Date) fieldValue);
                        break;
                    case Types.INTEGER:
                        prstm.setInt(prepIdx++, ((Integer) fieldValue).intValue());
                        break;
                    case Types.BIGINT:
                        prstm.setLong(prepIdx++, ((Long) fieldValue).longValue());
                        break;
                    case Types.DECIMAL:
                        prstm.setDouble(prepIdx++, ((Double) fieldValue).doubleValue());
                        break;
                    case Types.BINARY:
                        prstm.setObject(prepIdx++, fieldValue);
                        break;
                }
            }

        }

        return prstm;
    }

    /**
     * @param wrapper, DataWrapper
     * @param loaded, initialized ResultSet
     * TODO: change loop: no more in fields, use a loop in columns -> get field name, set field using dbname
     */
    private DataObject[] loadData(DataWrapper wrapper, ResultSet rs) throws IllegalAccessException, SQLException {
        Map columns = wrapper.getColumns();

        rs.last();
        DataObject[] dati = new DataObject[rs.getRow()];
        rs.beforeFirst();
        int idx = 0;
        if (columns != null) {
            DataObject rowData = null;
            while (rs.next()) {
                rowData = wrapper.getData();
                Field[] fields = rowData.getDataFields();
                Iterator iterColumns = columns.keySet().iterator();
                while (iterColumns.hasNext()) {
                    String colName = (String) iterColumns.next();
                    Column column = (Column) columns.get(colName);
                    // TODO: add here a "continue" if column keytypes are not for loading (eg. FILTER)
                    Field field = getField(fields, column.getName());
                    if (field == null) {
                        log(column.getName() + " has no fields");
                        continue;
                    }
                    // force the value of accessibility to avoid control (faster):
                    field.setAccessible(true);
                    int fieldType = column.datatype.getType();
                    String fieldDBName = column.getDBName();
                    //log("loadData-> field[" + idf + "]" + column.getName() + ":" + fieldType + "=" + field.get(rowData) + " is accessible: " + field.isAccessible());
                    switch (fieldType) {
                        case Types.CHAR:
                        case Types.VARCHAR:
                            field.set(rowData, rs.getString(fieldDBName));
                            break;
                        case Types.DATE:
                        case Types.TIME:
                            field.set(rowData, rs.getDate(fieldDBName));
                            break;
                        case Types.INTEGER:
                            field.set(rowData, new Integer(rs.getInt(fieldDBName)));
                            break;
                        case Types.BIGINT:
                            field.set(rowData, new Long(rs.getLong(fieldDBName)));
                            break;
                        case Types.DECIMAL:
                            field.set(rowData, new Double(rs.getDouble(fieldDBName)));
                            break;
                        case Types.BINARY:
                            field.set(rowData, rs.getObject(fieldDBName));
                            break;
                    }
                }
                dati[idx++] = rowData;
            }
        }
        return dati;
    }

    public String showData(DataObject data) {

        Field[] fields = data.getDataFields();
        StringBuffer datavalue = new StringBuffer(300);
        datavalue.append("[").append(data.getClass().getName()).append("]:\n");
        if (fields != null) {
            for (int idf = 0; idf < fields.length; idf++) {
                Field field = fields[idf];
                field.setAccessible(true);
                String fieldType = field.getType().getName();
                datavalue.append("\tfield[").append(idf).append("]:");
                datavalue.append(field.getName());
                datavalue.append("(").append(fieldType).append(")=");
                try {
                    datavalue.append(field.get(data));
                } catch (IllegalArgumentException iarexc) {
                    datavalue.append("IllegalArgumentException");
                } catch (IllegalAccessException iacexc) {
                    datavalue.append("IllegalAccessException");
                }
                datavalue.append(" accessible:").append(field.isAccessible()).append("\n");
            }
        }

        return datavalue.toString();
    }

    private Column getColumn(DataWrapper wrapper, String name) {
        Map columns = wrapper.getColumns();
        return (Column) columns.get(name);
        /*
        Column column = null;
        for (int idc = 0; idc < columns.length; idc++) {
        if (columns[idc].getName().equals(name)) {
        column = columns[idc];
        break;
        }
        }
        if (column == null) {
        throw new ArrayIndexOutOfBoundsException("Column " + name + " not found in current Columns array.");
        }
        return column;
         */
    }

    private Field getField(Field[] fields, String name) {
        Field field = null;
        for (int idc = 0; idc < fields.length; idc++) {
            if (fields[idc].getName().equals(name)) {
                field = fields[idc];
                break;
            }
        }
        if (field == null) {
            throw new ArrayIndexOutOfBoundsException("Field " + name + " not found in current Field array.");
        }
        return field;
    }

    public ResultSet loadEmptyResultSet(DataWrapper wrapper) throws SQLException {
        String query = wrapper.getSelectSQL();
        if (!query.contains("WHERE")) {
            query += " WHERE 1=0";
        }
        PreparedStatement prstm = getPreparedStatement(query);
        ResultSet rs = prstm.executeQuery();
        return rs;
    }

    public ResultSet loadResultSet(DataWrapper wrapper) throws SQLException {
        PreparedStatement prstm = getPreparedStatement(wrapper.getSelectSQL());
        ResultSet rs = prstm.executeQuery();
        return rs;
    }

    public static void close(ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
    }

    public static void close(PreparedStatement prstm) throws SQLException {
        if (prstm != null) {
            prstm.close();
        }
    }

    public static void close(Statement stmt) throws SQLException {
        if (stmt != null) {
            stmt.close();
        }
    }

    public void showConnectionInformations() throws SQLException {
        DatabaseMetaData metadata = cnn.getMetaData();

        log("Database name: " + metadata.getDatabaseProductName());
        log("Database version: " + metadata.getDatabaseProductVersion());
        log("Driver version: " + metadata.getDriverVersion());

    }

    public void showResultSetInformations(ResultSet rs) throws SQLException {
        showResultSetInformations(rs, false);
    }

    public void showResultSetInformations(ResultSet rs, boolean writeColumns) throws SQLException {
        ResultSetMetaData metadata = rs.getMetaData();
        int numColumns = metadata.getColumnCount();
        for (int idc = 1; idc < (numColumns + 1); idc++) {
            String columnName = metadata.getColumnName(idc);
            String columnTypeName = metadata.getColumnTypeName(idc);
            String columnClassName = metadata.getColumnClassName(idc);
            String columnTableName = metadata.getTableName(idc);
            int columnPrecision = metadata.getPrecision(idc);
            int columnScale = metadata.getScale(idc);
            if (writeColumns) {
                log("column(new Column(\"" + columnName + "\", \"" + columnName + "\", group(\"" + columnTableName + "\"), Datatypes." + columnTypeName + ", Keytypes.DESCRIPTION, Fieldtypes.UNDEFINED, " + columnPrecision + ", " + columnScale + ", \"Label" + columnName + "\"));");
            } else {
                //log(columnTypeName + "=" + Datatypes.mapDatatype(columnTypeName));
                log(columnClassName + " " + columnName + ": " + columnTypeName + " - " + " Precision: " + columnPrecision + ", " + " Scale: " + columnScale);
            }
        }
    }

    /**
     *
     * @param ResultSet to convert into map array
     * @return array of maps fieldName:fieldValue
     * @throws Exception
     */
    public Object[] toObject(ResultSet rs) throws Exception {
        List listRighe = new ArrayList();
        ResultSetMetaData meta = rs.getMetaData();
        // Load ResultSet into map by column name
        int numberOfColumns = meta.getColumnCount();
        while (rs.next()) {
            Map tmpMap = new HashMap();
            for (int idx = 1; idx <= numberOfColumns; ++idx) {
                String key = meta.getColumnName(idx);
                Object value = rs.getObject(idx);
                tmpMap.put(key, value);
            }
            listRighe.add(tmpMap);
        }
        return listRighe.toArray();
    }

    private void log(Object obj) {
        if (obj == null) {
            log("null");
        } else {
            log(obj.toString());
        }
    }

    private void log(String msg) {
        if (debug) {
            System.out.println("> " + msg);
        }
    }
}
